package com.ztjava.core.tool;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import org.apache.log4j.Logger;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.poifs.filesystem.OfficeXmlFileException;

import com.alibaba.fastjson.JSONObject;
import com.jfinal.kit.Ret;
import com.jfinal.plugin.activerecord.Model;
import com.jfinal.plugin.activerecord.Record;
import com.jfinal.plugin.redis.Cache;
import com.jfinal.plugin.redis.Redis;
import com.xiaoleilu.hutool.date.DatePattern;
import com.xiaoleilu.hutool.date.DateUtil;
import com.ztjava.core.Constant;

public class ExeclTool {
	private static Logger logger = Logger.getLogger(ExeclTool.class);

	@SuppressWarnings({ "rawtypes"})
	public static File dateToExecl(List list,String configName) {
		return dateToExecl(list, configName,null);
	}
	@SuppressWarnings("rawtypes")
	public static File dateToExecl(List list,String configName,String filename) {
		if(filename==null||filename.equals("")){
			filename = DateUtil.format(new Date(), "yyyyMMddHHmmss") + ".xls";
		}else {
			filename = filename + ".xls";
		}
		Cache cache = Redis.use();
		JSONObject config = cache.hget(Constant.R_PRE+"EXPORT", configName);
		if(config==null){
			logger.error("缺少"+configName+"导出配置！");
			return null;
		}
		return dateToExecl(list, config,filename);
	}
	/***
	 * 	"export":{
	 *			"JOB_FULL_RESUME":{
	 *			    "company_id_cn":{"name":"企业","width":"30"}
	 *			}		    
	 *	}
	 * @param list
	 * @param config
	 * @return
	 */
	@SuppressWarnings({ "rawtypes", "resource" })
	public static File dateToExecl(List list,JSONObject config,String filename) {
		File file = new File(filename);
		HSSFWorkbook hssfWorkbook = new HSSFWorkbook();
		HSSFSheet hssfSheet = hssfWorkbook.createSheet("sheet1");
		// 创建表头
		HSSFRow row = hssfSheet.createRow(0);
		HSSFCell cell = null;
		Integer t = 0;
		for (String key:config.keySet()) {
			cell = row.createCell(t);
			cell.setCellValue(config.getJSONObject(key).getString("name"));
			if (config.getJSONObject(key).get("width")!=null) {
				hssfSheet.setColumnWidth(t,config.getJSONObject(key).getInteger("width") * 256);
			} else {
				hssfSheet.setColumnWidth(t, 20 * 256);
			}
			t = t + 1;
		}
		Model model = null;
		JSONObject column = null;
		Integer count = 0;
		HSSFCellStyle stringToDateStyle = hssfWorkbook.createCellStyle();
		stringToDateStyle.setDataFormat(hssfWorkbook.createDataFormat().getFormat("yyyy/mm/dd"));
		
		for (int i=0,size=list.size();i<size;i++) {
			row = hssfSheet.createRow(++count);
			model = (Model) list.get(i);
			int j = 0;
			for (String key:config.keySet()) {
				column = config.getJSONObject(key);
				cell = row.createCell(j);
				j = j + 1;
				if (model.get(key) == null) {
					cell.setCellValue("-");
					continue;
				}
				if (column.get("type")!=null) {
					switch (column.getString("type")) {
					case "int":
						cell.setCellValue(model.getInt(key));
						break;
					case "decimal":
						cell.setCellValue(model.getBigDecimal(key).doubleValue());
						break;
					case "timestamp":
						cell.setCellValue(DateUtil.format(model.getTimestamp(key), DatePattern.NORM_DATETIME_PATTERN));
						break;
					case "stringToDate":
						cell.setCellValue(DateUtil.parse(model.getStr(key), DatePattern.NORM_DATE_PATTERN));
						cell.setCellStyle(stringToDateStyle);
						break;
					default:
						cell.setCellValue(model.get(key).toString());
						break;
					}
				} else {
					cell.setCellValue(model.get(key).toString());
				}
			}
		}
		try {
			FileOutputStream fileOutputStreane = new FileOutputStream(file);
			hssfWorkbook.write(fileOutputStreane);
			fileOutputStreane.flush();
			fileOutputStreane.close();
		} catch (FileNotFoundException e) {
			e.printStackTrace();
		} catch (IOException e) {
			e.printStackTrace();
		}
		return file;
	}

	// xls转换list
	@SuppressWarnings("resource")
	public static Ret execlToList(File file) {
		Ret ret = Ret.create();
		HSSFSheet sheet = null;
		HSSFWorkbook wookbook = null;
		try {
			wookbook = new HSSFWorkbook(new FileInputStream(file));
			sheet = wookbook.getSheetAt(0);
		} catch (OfficeXmlFileException e) {
			e.printStackTrace();
			ret.set(Constant.RESULT, Constant.ERROR);
			ret.set(Constant.MESSAGE, "必须使用xls格式的execl文档！");
			return ret;
		} catch (Exception e) {
			e.printStackTrace();
			ret.set(Constant.RESULT, Constant.ERROR);
			ret.set(Constant.MESSAGE, "execl解析出错！");
			return ret;
		}
		Integer rows = sheet.getPhysicalNumberOfRows();
		if (rows <= 1) {
			ret.set(Constant.RESULT, Constant.ERROR);
			ret.set(Constant.MESSAGE, "execl没有可导入的数据！");
			return ret;
		}
		Integer num = sheet.getRow(0).getPhysicalNumberOfCells();
		if (num == 0 || rows <= 1) {
			ret.set(Constant.RESULT, Constant.ERROR);
			ret.set(Constant.MESSAGE, "execl没有可导入的数据！");
			return ret;
		}
		HSSFRow firstRow = sheet.getRow(0);
		List<Record> columnList = new ArrayList<Record>();
		for (int i = 0; i < num; i++) {
			Record record = new Record();
			if (firstRow.getCell(i).toString().trim().equals("")) {
				continue;
			}
			record.set("name", firstRow.getCell(i).toString().trim());
			record.set("index", i);
			columnList.add(record);
		}
		HSSFCell cell = null;
		HSSFRow row = null;
		List<Record> dataList = new ArrayList<Record>();
		for (int j = 1; j < rows; j++) {
			row = sheet.getRow(j);
			//防止中间空行
			if(row==null) continue;
			Record record = new Record();
			boolean nodate = false;
			for (int i = 0; i < columnList.size(); i++) {
				cell = row.getCell(columnList.get(i).getInt("index"));
				if (i == 0
						&& (readCellToString(cell) == null || readCellToString(
								cell).equals(""))) {
					nodate = true;
					break;
				}
				record.set(columnList.get(i).getStr("name"),
						readCellToString(cell));
			}
			if (nodate) {
				continue;
			}
			dataList.add(record);
		}
		ret.set(Constant.RESULT, Constant.SUCCESS);
		ret.set("columnList", columnList);
		ret.set("dataList", dataList);
		return ret;
	}

	public static Ret checkColumn(List<Record> columnList, String... columns) {
		Ret ret = Ret.create();
		if (columnList.size() == 0 || columns.length == 0) {
			ret.set(Constant.RESULT, Constant.ERROR);
			ret.set(Constant.MESSAGE, "没有列!");
			return ret;
		}
		for (int i = 0; i < columns.length; i++) {
			boolean have = false;
			for (int j = 0; j < columnList.size(); j++) {
				if (columns[i].equals(columnList.get(j).getStr("name"))) {
					have = true;
					break;
				}
			}
			if (!have) {
				ret.set(Constant.RESULT, Constant.ERROR);
				ret.set(Constant.MESSAGE, "没找到“" + columns[i] + "”列");
				return ret;
			}
		}
		ret.set(Constant.RESULT, Constant.SUCCESS);
		return ret;
	}

	public static Ret checkdata(List<Record> dataList, String[]... rule) {
		Ret ret = Ret.create();
		if (dataList.size() == 0 || rule.length == 0) {
			ret.set(Constant.RESULT, Constant.ERROR);
			ret.set(Constant.MESSAGE, "没有数据或没有规则!");
			return ret;
		}
		Record data;
		for (int i = 0; i < dataList.size(); i++) {
			data = dataList.get(i);
			for (int j = 0; j < rule.length; j++) {
				switch (rule[j][1]) {
				case c_NOT_BLANK:
					if (data.get(rule[j][0]) == null
							|| data.getStr(rule[j][0]).equals("")) {
						ret.set(Constant.RESULT, Constant.ERROR);
						ret.set(Constant.MESSAGE, "第" + (i + 2) + "行“"
								+ rule[j][0] + "”不能为空！");
						return ret;
					}
					break;
				default:
					break;
				}
			}
		}
		ret.set(Constant.RESULT, Constant.SUCCESS);
		return ret;
	}

	public final static String c_NOT_BLANK = "c_NOT_BLANK";

	// 读取单元格内容
	@SuppressWarnings("deprecation")
	public static String readCellToString(HSSFCell cell) {
		if (cell != null) {
			switch (cell.getCellType()) {
			case HSSFCell.CELL_TYPE_NUMERIC:
				if (HSSFDateUtil.isCellDateFormatted(cell)) {
					SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
					return sdf.format(HSSFDateUtil.getJavaDate(cell
							.getNumericCellValue()));
				}
				cell.setCellType(HSSFCell.CELL_TYPE_STRING);
				return cell.getStringCellValue().trim();
			case HSSFCell.CELL_TYPE_STRING:
				return cell.getStringCellValue().trim();
			case HSSFCell.CELL_TYPE_FORMULA:
			    try {  
		            /* 
		             * 此处判断使用公式生成的字符串有问题，因为HSSFDateUtil.isCellDateFormatted(cell)判断过程中cell 
		             * .getNumericCellValue();方法会抛出java.lang.NumberFormatException异常 
		             */  
		             if (HSSFDateUtil.isCellDateFormatted(cell)) {  
		                Date date = cell.getDateCellValue();  
		                return (date.getYear() + 1900) + "-" + (date.getMonth() + 1) +"-" + date.getDate();  
		               
		             } else {  
		            	 return String.valueOf(cell.getNumericCellValue());  
		             }  
	             } catch (IllegalStateException e) {  
	            	 return String.valueOf(cell.getRichStringCellValue());  
	             }  
			case HSSFCell.CELL_TYPE_BLANK:
				return "";
			default:
				return null;
			}
		}
		return null;
	}

	public static boolean is_blank(HSSFRow row) {
		if (row.getCell(0) == null || readCellToString(row.getCell(0)) == null
				|| readCellToString(row.getCell(0)).toString().length() == 0) {
			if (row.getCell(1) == null
					|| readCellToString(row.getCell(1)) == null
					|| readCellToString(row.getCell(1)).toString().length() == 0) {
				return true;
			}
		}
		return false;
	}
}
